<table width="100%">
	<tr>
		<td width="15%"><?php echo CHtml::image(Yii::app()->request->baseUrl.'/images/logo.png'); ?></td>
		<td width="85%">
		<h4><strong>PT. ANUGERAH BERSAMA BERKAH ABADI</strong></h4>
		<h4><strong>General Ledger</strong></h4>
		<h4><strong> <?php echo $month." ".date('Y'); ?></strong></h4>
		</td>
	</tr>
</table>
<?php 
foreach($list_coa as $listcoa): 
$cari = "";
$rumus = "";
$coa = "";
$id_coa = "";
$sql = "";
$data = array();

$coa = $listcoa['code'];
		$id_coa = $listcoa['id'];
		
		$coa_exp = explode('.',$coa);
		$parent_coa = $coa_exp[0];
		$str_coa = $coa_exp[1];
		
		//if($str_coa=="00"){
			//$cari = " LIKE '".$parent_coa."%' ";
		//}else{
			$cari = " = '".$coa."' ";
	//	}
		//khusus bank
		if($coa == '10A200.02' || $coa == '10A200.03' || $coa == '10A200.04' || $coa == '10A200.05' || $coa == '10A200.06' || $coa == '10A200.00'){
			$sql = " 

select case when nota_note like 'RC%' then debet - kredit
	else 0
	end as debet
	,case when nota_note like 'VC%' then kredit - debet
	else 0
	end as kredit
	,nota_note
	,keterangan 
	,tgl_transaksi
	from (
select sum(@ xx.debet) as debet,sum( @ xx.kredit) as kredit,nota_note as nota_note,'-' as keterangan,xx.tgl_transaksi from 
		(
select coalesce(debet,0) as debet,coalesce(credit,0) kredit,jurnal_no as nota_note,description as keterangan

,to_char(trx_date,'dd-mm-yyyy') as tgl_transaksi  
from hist_jurnal a inner join mst_coa b on a.mst_account_id = b.id where b.code  = '".$coa."' 
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a 
inner join mst_coa b on a.coa_debet_id = b.id  
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  = '".$coa."'  AND 
a.nominal > 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  = '".$coa."'  AND 
a.nominal > 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  = '".$coa."'  AND 
 a.nominal < 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  = '".$coa."'  AND 
 a.nominal < 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  = '".$coa."'  and a.nominal > 0
union
select 0 as debet, coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  = '".$coa."'  and a.nominal > 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  = '".$coa."'  and a.nominal < 0
union
select coalesce(nominal,0) * c.konvert as debet, 0 as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  = '".$coa."'  and a.nominal < 0
  )xx group by nota_note,tgl_transaksi
 )zz ";
		}else{
		
		$rumus = substr($coa,0,3);
		if($rumus == '10A' || $rumus == '10B'){
			$sql = " select 
			case when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.kredit 
			else @ xx.debet
			end as debet
			,case when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.debet
			else @ xx.kredit
			end as kredit,xx.nota_note,xx.keterangan,xx.tgl_transaksi from ";
		}else{
			$sql = " select 
			case when nota_note like 'JN%' then @ xx.debet
			when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.kredit 
			else @ xx.kredit
			end as debet
			,case 
			when nota_note like 'JN%' then @ xx.kredit
			when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.debet
			else @ xx.debet
			end as kredit,xx.nota_note,xx.keterangan,xx.tgl_transaksi from ";
		}
		$sql .="
		(
select coalesce(debet,0) as debet,coalesce(credit,0) kredit,jurnal_no as nota_note,description as keterangan
,to_char(trx_date,'dd-mm-yyyy') as tgl_transaksi  
from hist_jurnal a inner join mst_coa b on a.mst_account_id = b.id where b.code ".$cari."
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a 
inner join mst_coa b on a.coa_debet_id = b.id  
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code ".$cari." AND 
a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code ".$cari." AND 
a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code ".$cari." AND 
 a.nominal < 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code ".$cari." AND 
 a.nominal < 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code ".$cari." and a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet, 0 as kredit,a.no_voucher as nota_note,nota_note as keterangan,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code ".$cari." and a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_voucher as nota_note,nota_note as keterangan,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code ".$cari." and a.nominal < 0
union
select 0 as debet, coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code ".$cari." and a.nominal < 0
 ";
 
 // HEALTH
 if($id_coa == '20'){ // jika dipilih piutang premi, maka select health & non health
		$sql .= " 
		union
		select 
		coalesce(a.nominal,0) * coalesce(c.nominal) debet,0 as kredit,a.nota_debet_no as nota_note
		,a.note as keterangan,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
		from ndk_health a 
		inner join reg_health_policy b on a.reg_health_policy_id = b.id
		INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
		INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID
		where posting = '2'
union
select 
		0 debet,coalesce(a.nominal,0) * coalesce(c.nominal) as kredit,a.nota_debet_no as nota_note
		,a.note as keterangan,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
		from ndk_health_kredit a 
		inner join reg_health_policy b on a.reg_health_policy_id = b.id
		INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
		INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID
		where posting = '2' 
union
select coalesce(nominal,0) as debet,0 as kredit, nota_no as nota_note, 'ganti_kartu' as keterangan
,to_char(createdtime,'dd-mm-yyyy') as tgl_transaksi 
from ganti_kartu where type_reg_policy = '1' ";

//NON HEALTH
		$sql .= "
		union
select
(a.premium + a.tpl + a.adjusment + a.policy_cost + a.stampduty_asuransi + a.admin_broker + a.stamp_duty -a.discount - a.diskon_asuransi) * coalesce(c.nominal) debet,0 kredit,a.nota_debet_no as nota_note
,a.note as keterangan,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
from
ndk_health_non a 
inner join reg_health_policy_non b on a.reg_nonhealth_policy_id = b.id
inner join mst_currency_detail c on b.MST_CURRENCY_DETAIL_ID = c.id
inner join MST_CURRENCY D ON C.CURRENCY_ID = D.ID
		where posting = '2'
union
select 
0 debet,coalesce(a.nominal,0) * coalesce(c.nominal) as kredit,a.nota_debet_no as nota_note
,a.note as keterangan,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
from ndk_health_non_kredit a 
inner join reg_health_policy_non b on a.reg_nonhealth_policy_id = b.id
INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID
where posting = '2'
union
select coalesce(nominal,0) as debet,0 as kredit, nota_no as nota_note, 'ganti_kartu' as keterangan
,to_char(createdtime,'dd-mm-yyyy') as tgl_transaksi 
from ganti_kartu where type_reg_policy = '2' ";
}


 if($id_coa=='24'){//jika dipilih HEALTH saja
	$sql .= " 
		union
		select 
		coalesce(a.nominal,0) * coalesce(c.nominal) debet,0 as kredit,a.nota_debet_no as nota_note
		,a.note as keterangan,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
		from ndk_health a 
		inner join reg_health_policy b on a.reg_health_policy_id = b.id
		INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
		INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID
		where posting = '2'
union
select 
		0 debet,coalesce(a.nominal,0) * coalesce(c.nominal) as kredit,a.nota_debet_no as nota_note
		,a.note as keterangan,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
		from ndk_health_kredit a 
		inner join reg_health_policy b on a.reg_health_policy_id = b.id
		INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
		INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID
		where posting = '2' 
union
select coalesce(nominal,0) as debet,0 as kredit, nota_no as nota_note, 'ganti_kartu' as keterangan
,to_char(createdtime,'dd-mm-yyyy') as tgl_transaksi 
from ganti_kartu where type_reg_policy = '1' ";
 }
 
 if($id_coa=='21' || $id_coa=='22' || $id_coa=='23' || $id_coa=='25' || $id_coa=='26'){//jika dipilih NON HEALTH saja
	$sql .= "
		union
	select
	(a.premium + a.tpl + a.adjusment + a.policy_cost + a.stampduty_asuransi + a.admin_broker + a.stamp_duty -a.discount - a.diskon_asuransi) * coalesce(c.nominal) as debet,0 as kredit,a.nota_debet_no as nota_note
	,a.note as keterangan,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
	from
	ndk_health_non a 
	inner join reg_health_policy_non b on a.reg_nonhealth_policy_id = b.id
	inner join mst_currency_detail c on b.MST_CURRENCY_DETAIL_ID = c.id
	inner join MST_CURRENCY D ON C.CURRENCY_ID = D.ID
			where posting = '2' ";
			
	if($id_coa=='21'){ //harta benda
		$sql .= " AND b.mst_jns_asuransi_id = '6' ";
	}
	
	if($id_coa=='22'){ //kendaraan bermotor
		$sql .= " AND b.mst_jns_asuransi_id = '9' ";
	}
	
	if($id_coa=='23'){ //kecelakaan diri
		$sql .= " AND b.mst_jns_asuransi_id = '11' ";
	}
	
	if($id_coa=='25'){ //jiwa
		$sql .= " AND b.mst_jns_asuransi_id = '12' ";
	}
	
	if($id_coa=='26'){ //aneka
		$sql .= " AND b.mst_jns_asuransi_id = '18' ";
	}
			
	$sql.= "
	union
	select 
	0 debet,coalesce(a.nominal,0) * coalesce(c.nominal) as kredit,a.nota_debet_no as nota_note
	,a.note as keterangan,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
	from ndk_health_non_kredit a 
	inner join reg_health_policy_non b on a.reg_nonhealth_policy_id = b.id
	INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
	INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID
	where posting = '2' ";
	
	if($id_coa=='21'){ //harta benda
		$sql .= " AND b.mst_jns_asuransi_id = '6' ";
	}
	
	if($id_coa=='22'){ //kendaraan bermotor
		$sql .= " AND b.mst_jns_asuransi_id = '9' ";
	}
	
	if($id_coa=='23'){ //kecelakaan diri
		$sql .= " AND b.mst_jns_asuransi_id = '11' ";
	}
	
	if($id_coa=='25'){ //jiwa
		$sql .= " AND b.mst_jns_asuransi_id = '12' ";
	}
	
	if($id_coa=='26'){ //aneka
		$sql .= " AND b.mst_jns_asuransi_id = '18' ";
	}
	
	$sql.= "
	union
	select coalesce(a.nominal,0) as debet,0 as kredit, a.nota_no as nota_note, 'ganti_kartu' as keterangan
	,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi 
	from ganti_kartu a 
	inner join reg_health_policy_non b on a.reg_policy_id = b.id
	where a.type_reg_policy = '2' ";
	
	if($id_coa=='21'){ //harta benda
		$sql .= " AND b.mst_jns_asuransi_id = '6' ";
	}
	
	if($id_coa=='22'){ //kendaraan bermotor
		$sql .= " AND b.mst_jns_asuransi_id = '9' ";
	}
	
	if($id_coa=='23'){ //kecelakaan diri
		$sql .= " AND b.mst_jns_asuransi_id = '11' ";
	}
	
	if($id_coa=='25'){ //jiwa
		$sql .= " AND b.mst_jns_asuransi_id = '12' ";
	}
	
	if($id_coa=='26'){ //aneka
		$sql .= " AND b.mst_jns_asuransi_id = '18' ";
	}
 }

 
 
 if($id_coa == '61'){ // jika dipilih piutang pajak pph 23
		$sql .= " 
		union
		select 
		coalesce(a.pph,0) * coalesce(c.nominal) as debet,0 kredit,'PPh-'||a.nota_debet_no as nota_note,a.note as keterangan
		,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
		from ndk_health a 
		inner join reg_health_policy b on a.reg_health_policy_id = b.id
		INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
		INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID where posting = '2'
union
select 
		coalesce(a.pph,0) * coalesce(c.nominal) as debet,0 kredit,'PPh-'||a.nota_debet_no as nota_note,a.note as keterangan
		,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
		from ndk_health_non a 
		inner join reg_health_policy_non b on a.reg_nonhealth_policy_id = b.id
		INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
		INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID where posting = '2' 
union
select 
		0 as debet,coalesce(a.pph,0) * coalesce(c.nominal) kredit,'PPh-'||a.nota_debet_no as nota_note,a.note as keterangan
		,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
		from ndk_health_kredit a 
		inner join reg_health_policy b on a.reg_health_policy_id = b.id
		INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
		INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID
		where posting = '2' 
union
select 
	0 as debet,coalesce(a.pph,0) * coalesce(c.nominal) kredit,'PPh-'||a.nota_debet_no as nota_note,a.note as keterangan
		,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
	from ndk_health_non_kredit a 
	inner join reg_health_policy_non b on a.reg_nonhealth_policy_id = b.id
	INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
	INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID
	where posting = '2'	
 ";

}

 if($id_coa == '105'){ // jika dipilih hutang pajak ppn
		$sql .= " union
select 
		coalesce(a.ppn,0) * coalesce(c.nominal)  as debet,0 as kredit,'PPn-'||a.nota_debet_no as nota_note,a.note as keterangan
		,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
		from ndk_health a 
		inner join reg_health_policy b on a.reg_health_policy_id = b.id
		INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
		INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID where posting = '2'
union
select 
		coalesce(a.ppn,0) * coalesce(c.nominal) as debet,0 kredit,'PPn-'||a.nota_debet_no as nota_note,a.note as keterangan
		,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
		from ndk_health_non a 
		inner join reg_health_policy_non b on a.reg_nonhealth_policy_id = b.id
		INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
		INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID where posting = '2' 
union
select 
		0 as debet,coalesce(a.ppn,0) * coalesce(c.nominal) as kredit,'PPn-'||a.nota_debet_no as nota_note,a.note as keterangan
		,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
		from ndk_health_kredit a 
		inner join reg_health_policy b on a.reg_health_policy_id = b.id
		INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
		INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID
		where posting = '2' 
union
select 
	0 as debet,coalesce(a.ppn,0) * coalesce(c.nominal) kredit,'PPn-'||a.nota_debet_no as nota_note,a.note as keterangan
		,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
	from ndk_health_non_kredit a 
	inner join reg_health_policy_non b on a.reg_nonhealth_policy_id = b.id
	INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
	INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID
	where posting = '2'		



	
		";
}

 if($id_coa == '97'){ // jika dipilih hutang premi health
$sql .= " 
union
select 
		 coalesce(a.nett_to_insurance,0) * coalesce(c.nominal) as debet,0 as kredit,a.nota_debet_no as nota_note,a.note as keterangan
		,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
		from ndk_health a 
		inner join reg_health_policy b on a.reg_health_policy_id = b.id
		INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
		INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID where posting = '2'
union
select 
		0 debet,coalesce(a.nett_to_insurance,0) * coalesce(c.nominal) as kredit,a.nota_debet_no as nota_note
		,a.note as keterangan,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
		from ndk_health_kredit a 
		inner join reg_health_policy b on a.reg_health_policy_id = b.id
		INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
		INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID
		where posting = '2' 
";
}//end hutang premi health

 if($id_coa == '94' || $id_coa == '95' || $id_coa == '96' || $id_coa == '98' || $id_coa == '99'){ // jika dipilih hutang premi non health
 $sql .= "
union
select 
		((a.premium + a.tpl - a.diskon_asuransi + a.policy_cost + a.stampduty_asuransi - a.brokerage - a.ppn + a.pph) * coalesce(c.nominal) ) + (coalesce(a.tpl,0) * coalesce(c.nominal) )
			as debet
		,0  as kredit,a.nota_debet_no as nota_note,a.note as keterangan
		,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
		from ndk_health_non a 
		inner join reg_health_policy_non b on a.reg_nonhealth_policy_id = b.id
		INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
		INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID where posting = '2' ";
		if($id_coa=='94'){ //harta benda
		$sql .= " AND b.mst_jns_asuransi_id = '6' ";
	}
	
	if($id_coa=='95'){ //kendaraan bermotor
		$sql .= " AND b.mst_jns_asuransi_id = '9' ";
	}
	
	if($id_coa=='96'){ //kecelakaan diri
		$sql .= " AND b.mst_jns_asuransi_id = '11' ";
	}
	
	if($id_coa=='98'){ //jiwa
		$sql .= " AND b.mst_jns_asuransi_id = '12' ";
	}
	
	if($id_coa=='99'){ //aneka
		$sql .= " AND b.mst_jns_asuransi_id = '18' ";
	}
	
	 $sql .= "
union
select 
	0 debet,coalesce(a.nett_to_insurance,0) * coalesce(c.nominal) as kredit,a.nota_debet_no as nota_note
	,a.note as keterangan,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
	from ndk_health_non_kredit a 
	inner join reg_health_policy_non b on a.reg_nonhealth_policy_id = b.id
	INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
	INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID
	where posting = '2'  ";
		if($id_coa=='94'){ //harta benda
		$sql .= " AND b.mst_jns_asuransi_id = '6' ";
	}
	
	if($id_coa=='95'){ //kendaraan bermotor
		$sql .= " AND b.mst_jns_asuransi_id = '9' ";
	}
	
	if($id_coa=='96'){ //kecelakaan diri
		$sql .= " AND b.mst_jns_asuransi_id = '11' ";
	}
	
	if($id_coa=='98'){ //jiwa
		$sql .= " AND b.mst_jns_asuransi_id = '12' ";
	}
	
	if($id_coa=='99'){ //aneka
		$sql .= " AND b.mst_jns_asuransi_id = '18' ";
	}
	
	 $sql .= "
union
	select 0 as debet,coalesce(a.nominal,0) as kredit, a.nota_no as nota_note, 'ganti_kartu' as keterangan
	,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi 
	from ganti_kartu a 
 ";
}

 if($id_coa == '130'){ // jika dipilih R/L - pendapatan jasa keperantaraan health
$sql .= " 
union
select 
	coalesce(brokerage,0) * c.nominal - coalesce(discount,0) * c.nominal + coalesce(adjusment,0) * c.nominal + 
	coalesce(diskon_asuransi,0) * c.nominal as debet,0 kredit,a.nota_debet_no as nota_note,a.note as keterangan
		,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
	from ndk_health a 
	inner join reg_health_policy b on a.reg_health_policy_id = b.id
	INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
	INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID where posting = '2'
union
select 
		0 as debet,coalesce(brokerage,0) * c.nominal - coalesce(discount,0) * c.nominal + coalesce(adjusment,0) * c.nominal + 
	coalesce(diskon_asuransi,0) * c.nominal kredit,a.nota_debet_no as nota_note,a.note as keterangan
		,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
		from ndk_health_kredit a 
		inner join reg_health_policy b on a.reg_health_policy_id = b.id
		INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
		INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID
		where posting = '2'
 ";
}
if($id_coa == '127' || $id_coa == '128' || $id_coa == '129' || $id_coa == '131' || $id_coa == '132'){ // jika dipilih R/L - pendapatan jasa keperantaraan health
$sql .=" 
union
select 
	coalesce(brokerage,0) * c.nominal - coalesce(discount,0) * c.nominal + coalesce(adjusment,0) * c.nominal + 
	coalesce(diskon_asuransi,0) * c.nominal as debet,0 as kredit,a.nota_debet_no as nota_note,a.note as keterangan
		,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
	from ndk_health_non a 
	inner join reg_health_policy_non b on a.reg_nonhealth_policy_id = b.id
	INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
	INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID where posting = '2'
  ";
		if($id_coa=='127'){ //harta benda
		$sql .= " AND b.mst_jns_asuransi_id = '6' ";
	}
	
	if($id_coa=='128'){ //kendaraan bermotor
		$sql .= " AND b.mst_jns_asuransi_id = '9' ";
	}
	
	if($id_coa=='129'){ //kecelakaan diri
		$sql .= " AND b.mst_jns_asuransi_id = '11' ";
	}
	
	if($id_coa=='131'){ //jiwa
		$sql .= " AND b.mst_jns_asuransi_id = '12' ";
	}
	
	if($id_coa=='132'){ //aneka
		$sql .= " AND b.mst_jns_asuransi_id = '18' ";
	}
	$sql .="
union
select 
	0 as debet,coalesce(brokerage,0) * c.nominal - coalesce(discount,0) * c.nominal + coalesce(adjusment,0) * c.nominal + 
	coalesce(diskon_asuransi,0) * c.nominal as kredit,a.nota_debet_no as nota_note,a.note as keterangan
		,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
	from ndk_health_non_kredit a 
	inner join reg_health_policy_non b on a.reg_nonhealth_policy_id = b.id
	INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
	INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID
	where posting = '2'
	 ";
	if($id_coa=='127'){ //harta benda
		$sql .= " AND b.mst_jns_asuransi_id = '6' ";
	}
	
	if($id_coa=='128'){ //kendaraan bermotor
		$sql .= " AND b.mst_jns_asuransi_id = '9' ";
	}
	
	if($id_coa=='129'){ //kecelakaan diri
		$sql .= " AND b.mst_jns_asuransi_id = '11' ";
	}
	
	if($id_coa=='131'){ //jiwa
		$sql .= " AND b.mst_jns_asuransi_id = '12' ";
	}
	
	if($id_coa=='132'){ //aneka
		$sql .= " AND b.mst_jns_asuransi_id = '18' ";
	}
	
}

 if($id_coa == '198'){ // jika dipilih R/L - hasil lainnya - Lainnya
$sql .= " 
union
select 
	coalesce(admin_broker,0) * c.nominal + coalesce(stamp_duty,0) * c.nominal as debet,0 kredit,a.nota_debet_no as nota_note,a.note as keterangan
		,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
	from ndk_health a 
	inner join reg_health_policy b on a.reg_health_policy_id = b.id
	INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
	INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID
	where posting = '2'
union
select 
	coalesce(admin_broker,0) * c.nominal + coalesce(stamp_duty,0) * c.nominal as debet,0 kredit,a.nota_debet_no as nota_note,a.note as keterangan
		,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
	from ndk_health_non a 
	inner join reg_health_policy_non b on a.reg_nonhealth_policy_id = b.id
	INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
	INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID
	where posting = '2'
union
select 
		0 as debet,coalesce(admin_broker,0) * c.nominal + coalesce(stamp_duty,0) * c.nominal kredit,a.nota_debet_no as nota_note,a.note as keterangan
		,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
		from ndk_health_kredit a 
		inner join reg_health_policy b on a.reg_health_policy_id = b.id
		INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
		INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID
		where posting = '2' 
union
select 
	0 as debet,coalesce(admin_broker,0) * c.nominal + coalesce(stamp_duty,0) * c.nominal kredit,a.nota_debet_no as nota_note,a.note as keterangan
		,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
	from ndk_health_non_kredit a 
	inner join reg_health_policy_non b on a.reg_nonhealth_policy_id = b.id
	INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
	INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID
	where posting = '2'	
	";
}

$sql .= " )xx order by nota_note	 ";
}
//echo $sql;exit;
	 
	$command = Yii::app()->db->createCommand($sql);
	$data = $command->queryAll();
	if(count($data) >=1){
		?>
		
Account Name : <?=$listcoa['name']."(".$listcoa['code'].")";?>
<div class="form" style="page-break-after:always">
<table border="1">
	<thead>
		<tr>
			<th>Date</th>
			<th>Ref. No.</th>
			<th>Description</th>
			<th>Debet</th>
			<th>Kredit</th>
		</tr>
	</thead>
	<tbody id="isi_cicilan">
	<?php 
		$debet = 0;$kredit=0;
		foreach($data as $item):
		$debet += $item['debet'];
		$kredit += $item['kredit'];
	?>
		<tr>
			<td><?=$item['tgl_transaksi']?></td>
			<td><?=$item['nota_note']?></td>
			<td><?=$item['keterangan']?></td>
			<td align="right"><?=number_format($item['debet'],2)?></td>
			<td align="right"><?=number_format($item['kredit'],2)?></td>
		</tr>
	<?php endforeach; ?>
		<tr>
			<td colspan="3" align="right">Total</td>
			<td align="right"><?php echo number_format($debet,2) ?></td>
			<td align="right"><?php echo number_format($kredit,2) ?></td>
		</tr>
		<tr>
			<td colspan="3" align="right">Grand Total</td>
			<td colspan="2" align="right"><?php 
			/*if($debet < $kredit){
				echo "(-)";
			}*/
			echo number_format($debet - $kredit); 
			?></td>
		</tr>
	</tbody>
</table>	
</div>
<br/><br/>
		<?
	}//end cek ada data
?>

<?php endforeach; ?>
